package com.bmadmin.service.impl;

import com.bmadmin.domain.*;
import com.bmadmin.mapper.DataCustTableBodyDao;
import com.bmadmin.mapper.DataCustTableHeadDao;
import com.bmadmin.service.DataTableForCustomerService;
import com.bmadmin.util.BMUtil;
import com.bmadmin.util.DataSource;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

import javax.management.relation.RelationSupport;
import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;

import static com.bmadmin.util.Util.resultSetToJson;

@Service
@Transactional
public class DataTableForCustomerServiceImpl implements DataTableForCustomerService {
    @Autowired
    DataCustTableHeadDao dataCustTableHeadDao;
    @Autowired
    DataCustTableBodyDao dataCustTableBodyDao;

    @Override
    public NormalResult getTableList() {
        NormalResult result = new NormalResult();
        List<PluginZtreeDataPOJO> pluginZtreeDataPOJOS = new ArrayList<PluginZtreeDataPOJO>();

        DataCustTableHeadPOJO dataCustTableHeadPOJOForQuery = new DataCustTableHeadPOJO();
        List<DataCustTableHeadPOJO> dataCustTableHeadPOJOS = dataCustTableHeadDao.find(dataCustTableHeadPOJOForQuery);

        for(DataCustTableHeadPOJO headPOJOItem:dataCustTableHeadPOJOS){
            PluginZtreeDataPOJO pluginZtreeDataPOJO = new PluginZtreeDataPOJO();
            pluginZtreeDataPOJO.setId(headPOJOItem.getT_id());
            pluginZtreeDataPOJO.setName(headPOJOItem.getT_name());
            pluginZtreeDataPOJO.setPart_id("99999");
            pluginZtreeDataPOJO.setClick("getLineClick('" + headPOJOItem.getT_id() + "')");

            pluginZtreeDataPOJOS.add(pluginZtreeDataPOJO);
        }

        PluginZtreeDataPOJO levelOne = new PluginZtreeDataPOJO();
        levelOne.setId("99999");
        levelOne.setName("自建表");
        levelOne.setOpen(true);
        levelOne.setClick("getLineClick('99999')");
        levelOne.setChildren(pluginZtreeDataPOJOS);

        result.setCode("0000");
        result.setMsg("查询成功");
        result.setObject(levelOne);

        return result;
    }

    @Override
    public NormalResult getTableHeadByTableId(DataCustTableHeadPOJO dataCustTableHeadPOJO) {
        NormalResult result = new NormalResult();

        List<DataCustTableHeadPOJO> dataCustTableHeadPOJOS = dataCustTableHeadDao.find(dataCustTableHeadPOJO);
        if(dataCustTableHeadPOJOS.size()!=1){

            result.setCode("9999");
            result.setMsg("数据异常，请联系系统管理员或重新进入系统");
            return result;
        }

        DataCustTableHeadPOJO dataCustTableHeadResult= dataCustTableHeadPOJOS.get(0);

        DataCustTableBodyPOJO dataCustTableBodyPOJO = new DataCustTableBodyPOJO();
        dataCustTableBodyPOJO.setB_id(dataCustTableHeadPOJO.getT_id());
        List<DataCustTableBodyPOJO> dataCustTableBodyPOJOS = dataCustTableBodyDao.find(dataCustTableBodyPOJO);

        if(dataCustTableBodyPOJOS.size()>0){
            dataCustTableHeadResult.setChildrens(dataCustTableBodyPOJOS);
        }

        result.setCode("0000");
        result.setMsg("查询成功");
        result.setObject(dataCustTableHeadResult);

        return result;
    }

    @Override
    public LayUiDataTablePOJO getTableBodyByTableId(DataCustTableBodyPOJO dataCustTableBodyPOJO) {
        LayUiDataTablePOJO result = new LayUiDataTablePOJO();

        List<DataCustTableBodyPOJO> dataCustTableBodyPOJOS = dataCustTableBodyDao.find(dataCustTableBodyPOJO);

        result.setCode(0);
        result.setMsg("查询成功");
        result.setCount(dataCustTableBodyPOJOS.size());
        result.setData(dataCustTableBodyPOJOS);


        return result;
    }

    @Override
    public NormalResult insertTableHead(DataCustTableHeadPOJO dataCustTableHeadPOJO) {
        NormalResult result = new NormalResult();

        DataCustTableHeadPOJO dataCustTableHeadPOJOForQuery = new DataCustTableHeadPOJO();
        dataCustTableHeadPOJOForQuery.setT_name(dataCustTableHeadPOJO.getT_name());

        List<DataCustTableHeadPOJO> dataCustTableHeadPOJOS = dataCustTableHeadDao.find(dataCustTableHeadPOJOForQuery);
        if(dataCustTableHeadPOJOS.size()>0){
            result.setCode("9999");
            result.setMsg("表名已存在");
            return result;
        }

        dataCustTableHeadPOJO.setT_create_time(BMUtil.getNowTimeForString());
        int insertResult = dataCustTableHeadDao.insertSelective(dataCustTableHeadPOJO);
        if(insertResult!=1){
            result.setCode("9999");
            result.setMsg("表插入不成功");
            return result;
        }

        //TODO-在数据库执行相应表的创建

        String sql = "";
        sql = "create table "+dataCustTableHeadPOJO.getT_name()+" ( id INT PRIMARY KEY AUTO_INCREMENT);";
        if(BMUtil.isNotNull(dataCustTableHeadPOJO.getT_comment())){
            sql += "alter table " + dataCustTableHeadPOJO.getT_name() + " comment = '"+dataCustTableHeadPOJO.getT_comment()+"'";
        }

        Connection conn = DataSource.getConnOra("com.mysql.jdbc.Driver","jdbc:mysql://101.132.151.53:3306/hzwgs","hzwgs","hzwgs_123");
        Statement smt = null;
        try {
            smt = conn.createStatement();

             smt.execute(sql);
//            if(!execute){
//                result.setCode("9999");
//                result.setMsg("执行表语句异常,请联系系统管理员");
//                return result;
//            }
        } catch (SQLException e) {
            e.printStackTrace();
            result.setCode("9999");
            result.setMsg("执行表语句异常,请联系系统管理员");
            return result;
        }

        result.setCode("0000");
        result.setMsg("新建表成功");

        return result;
    }

    @Override
    public NormalResult deleteTableHeadAndBody(DataCustTableHeadPOJO dataCustTableHeadPOJO) {
        NormalResult result = new NormalResult();

        dataCustTableHeadDao.deleteByT_id(dataCustTableHeadPOJO.getT_id());
        dataCustTableBodyDao.deleteByT_id(dataCustTableHeadPOJO.getT_id());

        //TODO-在数据库执行相应操作

        String sql = "";
        sql = "drop table "+dataCustTableHeadPOJO.getT_name();


        Connection conn = DataSource.getConnOra("com.mysql.jdbc.Driver","jdbc:mysql://101.132.151.53:3306/hzwgs","hzwgs","hzwgs_123");
        Statement smt = null;
        try {
            smt = conn.createStatement();

            smt.execute(sql);
//            if(!execute){
//                result.setCode("9999");
//                result.setMsg("执行表语句异常,请联系系统管理员");
//                return result;
//            }
        } catch (SQLException e) {
            e.printStackTrace();
            result.setCode("9999");
            result.setMsg("执行表语句异常,请联系系统管理员");
            return result;
        }

        result.setCode("0000");
        result.setMsg("删除表成功");
        return result;
    }

    @Override
    public NormalResult updateTableHead(DataCustTableHeadPOJO dataCustTableHeadPOJO) {
        NormalResult result = new NormalResult();

        DataCustTableHeadPOJO dataCustTableHeadPOJOForQuery = new DataCustTableHeadPOJO();
        dataCustTableHeadPOJOForQuery.setT_name(dataCustTableHeadPOJO.getT_name());
        List<DataCustTableHeadPOJO> dataCustTableHeadPOJOS = dataCustTableHeadDao.find(dataCustTableHeadPOJOForQuery);
        if(dataCustTableHeadPOJOS.size()>0){
            if(!dataCustTableHeadPOJOS.get(0).getT_id().equals(dataCustTableHeadPOJO.getT_id())){
                result.setCode("9999");
                result.setMsg("表名已存在");
            }
        }

        dataCustTableHeadPOJO.setT_update_time(BMUtil.getNowTimeForString());
        dataCustTableHeadDao.update(dataCustTableHeadPOJO);

        //TODO-在数据库执行相应操作
        String sql = "";
//        sql = "alter table "+dataCustTableHeadPOJO.getT_name()+" rename to "+;


        Connection conn = DataSource.getConnOra("com.mysql.jdbc.Driver","jdbc:mysql://101.132.151.53:3306/hzwgs","hzwgs","hzwgs_123");
        Statement smt = null;
        try {
            smt = conn.createStatement();

            smt.execute(sql);
//            if(!execute){
//                result.setCode("9999");
//                result.setMsg("执行表语句异常,请联系系统管理员");
//                return result;
//            }
        } catch (SQLException e) {
            e.printStackTrace();
            result.setCode("9999");
            result.setMsg("执行表语句异常,请联系系统管理员");
            return result;
        }

        result.setCode("0000");
        result.setMsg("修改成功");
        return result;
    }

    @Override
    public NormalResult insertTableBody(DataCustTableBodyPOJO dataCustTableBodyPOJO) {
        NormalResult result = new NormalResult();

        DataCustTableBodyPOJO dataCustTableBodyPOJOForQuery = new DataCustTableBodyPOJO();
        dataCustTableBodyPOJOForQuery.setB_name(dataCustTableBodyPOJO.getB_name());
        dataCustTableBodyPOJOForQuery.setT_id(dataCustTableBodyPOJO.getT_id());
        List<DataCustTableBodyPOJO> dataCustTableBodyPOJOS = dataCustTableBodyDao.find(dataCustTableBodyPOJOForQuery);
        if(dataCustTableBodyPOJOS.size()>0){
            result.setCode("9999");
            result.setMsg("该字段已存在");
            return result;
        }

        int insertResult = dataCustTableBodyDao.insertSelective(dataCustTableBodyPOJO);
        if(insertResult!=1){
            result.setCode("9999");
            result.setMsg("表体字段插入异常");
            return result;
        }

        //TODO-在数据库执行相应操作




        result.setCode("0000");
        result.setMsg("表体字段新增成功");

        return result;
    }

    @Override
    public NormalResult deleteTableBody(DataCustTableBodyPOJO dataCustTableBodyPOJO) {
        NormalResult result = new NormalResult();

        int deleteResult = dataCustTableBodyDao.deleteByB_id(dataCustTableBodyPOJO.getB_id());
        if(deleteResult!=1){
            result.setCode("9999");
            result.setMsg("表体字段删除异常");
            return result;
        }

        //TODO-在数据库执行相应操作

        result.setCode("0000");
        result.setMsg("表体字段删除键成功");

        return result;
    }

    @Override
    public NormalResult updateTableBody(DataCustTableBodyPOJO dataCustTableBodyPOJO) {
        NormalResult result = new NormalResult();

        //TODO-表中已经存在数据不得进行修改操作。

        //字段名重复校验
        DataCustTableBodyPOJO dataCustTableBodyPOJOForQuery = new DataCustTableBodyPOJO();
        dataCustTableBodyPOJOForQuery.setB_name(dataCustTableBodyPOJO.getB_name());
        dataCustTableBodyPOJOForQuery.setT_id(dataCustTableBodyPOJO.getT_id());
        List<DataCustTableBodyPOJO> dataCustTableBodyPOJOS = dataCustTableBodyDao.find(dataCustTableBodyPOJOForQuery);

        if(dataCustTableBodyPOJOS.size()>0){
            if(!dataCustTableBodyPOJOS.get(0).getB_id().equals(dataCustTableBodyPOJO.getB_id())){
                result.setCode("9999");
                result.setMsg("该字段已经存在");
                return result;
            }
        }

        int updateResult = dataCustTableBodyDao.update(dataCustTableBodyPOJO);
        if(updateResult!=1){
            result.setCode("9999");
            result.setMsg("表体字段修改异常");
            return result;
        }
        //TODO-在数据库执行相应操作
        result.setCode("0000");
        result.setMsg("表体字段修改成功");
        return result;
    }

}
